﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Infragistics.Win.UltraWinGrid;
using System.Data;
using System.Data.SqlClient;

namespace AutoParkingManagement
{
    public partial class SearchCar : Form
    {
        #region Private members
        private UltraGridRow _selectedRow;

        private DataTable SearchData(object cardTypeID, object plateNumber,
                                        object entryFrom, object entryTo, object exitFrom, object exitTo)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT [ID], e.[CardID],[PlateNumber],[FrontImageEntry],[RearImageEntry], ");
            sb.Append("[FrontImageExit],[RearImageExit],[EntryTime],[ExitTime] ");
            sb.Append("FROM EntryExit e INNER JOIN [Card] c ON e.CardID = c.CardID ");
            sb.Append("INNER JOIN CardType t ON c.CardTypeID = t.CardTypeID ");
            sb.Append("WHERE (@CardTypeID IS NULL OR c.CardTypeID =@CardTypeID) ");
            sb.Append("AND (@PlateNumber IS NULL OR dbo.ufn_StripCharacters(PlateNumber, '^a-z0-9') LIKE '%' + dbo.ufn_StripCharacters(@PlateNumber, '^a-z0-9') + '%') ");
            if (entryFrom != DBNull.Value && entryTo != DBNull.Value)
            {
                DateTime dtFrom = (DateTime)entryFrom;
                entryFrom = new DateTime(dtFrom.Year,dtFrom.Month,dtFrom.Day,0,0,0);
                DateTime dtTo = (DateTime)entryTo;
                entryTo = new DateTime(dtTo.Year, dtTo.Month, dtTo.Day, 23, 59, 59);
                sb.Append("AND (EntryTime BETWEEN @EntryFrom AND @EntryTo) ");
            }
            else if(entryFrom!=DBNull.Value)
            {
                DateTime dtFrom = (DateTime)entryFrom;
                entryFrom = new DateTime(dtFrom.Year, dtFrom.Month, dtFrom.Day, 0, 0, 0);
                sb.Append("AND (EntryTime > @EntryFrom) ");
            }
            else if (entryTo != DBNull.Value)
            {
                DateTime dtTo = (DateTime)entryTo;
                entryTo = new DateTime(dtTo.Year, dtTo.Month, dtTo.Day, 23, 59, 59);
                sb.Append("AND (EntryTime < @EntryTo) ");
            }

            if (exitFrom != DBNull.Value && exitTo != DBNull.Value)
            {
                DateTime dtFrom = (DateTime)exitFrom;
                exitFrom = new DateTime(dtFrom.Year, dtFrom.Month, dtFrom.Day, 0, 0, 0);
                DateTime dtTo = (DateTime)exitTo;
                exitTo = new DateTime(dtTo.Year, dtTo.Month, dtTo.Day, 23, 59, 59);
                sb.Append("AND (ExitTime BETWEEN @ExitFrom AND @ExitTo) ");
            }
            else if (exitFrom != DBNull.Value)
            {
                DateTime dtFrom = (DateTime)exitFrom;
                exitFrom = new DateTime(dtFrom.Year, dtFrom.Month, dtFrom.Day, 0, 0, 0);
                sb.Append("AND (ExitTime > @ExitFrom ) ");
            }
            else if (exitTo != DBNull.Value)
            {
                DateTime dtTo = (DateTime)exitTo;
                exitTo = new DateTime(dtTo.Year, dtTo.Month, dtTo.Day, 23, 59, 59);
                sb.Append("AND (ExitTime < @ExitTo ) ");
            }

            string strOrderBy = String.Empty;
            if (entryFrom != DBNull.Value || entryTo != DBNull.Value)
            {
                strOrderBy = " ORDER BY EntryTime DESC";
            }

            if (exitFrom != DBNull.Value || exitTo != DBNull.Value)
            {
                if (String.IsNullOrEmpty(strOrderBy))
                {
                    strOrderBy = " ORDER BY ExitTime DESC";
                }
                else
                {
                    strOrderBy  += ", ExitTime DESC";
                }
                
            }

            if (String.IsNullOrEmpty(strOrderBy))
            {
                strOrderBy = " ORDER BY EntryTime DESC, ExitTime DESC";
            }

            sb.Append(strOrderBy);

            string strCommandText = sb.ToString();
            SqlParameter[] parms = new SqlParameter[6];

            parms[0] = new SqlParameter("@CardTypeID", SqlDbType.UniqueIdentifier);            
            parms[0].Value = cardTypeID;

            parms[1] = new SqlParameter("@PlateNumber",SqlDbType.NVarChar,10);            
            parms[1].Value = plateNumber;

            parms[2] = new SqlParameter("@EntryFrom", SqlDbType.DateTime);
            parms[2].Value = entryFrom;

            parms[3] = new SqlParameter("@EntryTo", SqlDbType.DateTime);
            parms[3].Value = entryTo;

            parms[4] = new SqlParameter("@ExitFrom", SqlDbType.DateTime);
            parms[4].Value = exitFrom;

            parms[5] = new SqlParameter("@ExitTo", SqlDbType.DateTime);
            parms[5].Value = exitTo;

            return SqlHelper.ExecuteDataTable(CommandType.Text, strCommandText, parms);
        }

        /// <summary>
        /// Reset search conditions
        /// </summary>
        private void Reset()
        {
            
        }
        #endregion

        #region Properties
        /// <summary>
        /// Selected row from Data grid view
        /// </summary>
        public UltraGridRow SelectedRow
        {
            get
            {
                return _selectedRow;
            }
        }    
        #endregion

        #region Public members

        #endregion

        #region Events
        private void SearchCar_Load(object sender, EventArgs e)
        {
            cboCardType.DataSource = Common.GetCardTypes(true);
            cboCardType.DisplayMember = "Name";
            cboCardType.ValueMember = "CardTypeID";
        }
        private void grpSearch_ExpandedStateChanging(object sender, CancelEventArgs e)
        {
            e.Cancel = true;
        }

        private void grpResult_ExpandedStateChanging(object sender, CancelEventArgs e)
        {
            grpSearch_ExpandedStateChanging(null, null);
        }

        private void grpEntrance_ExpandedStateChanging(object sender, CancelEventArgs e)
        {
            grpSearch_ExpandedStateChanging(null, null);
        }

        private void grpExit_ExpandedStateChanging(object sender, CancelEventArgs e)
        {
            grpSearch_ExpandedStateChanging(null, null);
        }

        private void grdResult_InitializeLayout(object sender, Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs e)
        {
            grdResult.DisplayLayout.Scrollbars = Infragistics.Win.UltraWinGrid.Scrollbars.Both;
            grdResult.DisplayLayout.Override.AllowRowFiltering = Infragistics.Win.DefaultableBoolean.True;
            grdResult.DisplayLayout.Bands[0].Columns["ID"].Hidden = true;
            grdResult.DisplayLayout.Bands[0].Columns["CardId"].Hidden = true;            
            grdResult.DisplayLayout.Bands[0].Columns["PlateNumber"].Header.Caption = "Biển số";
            grdResult.DisplayLayout.Bands[0].Columns["PlateNumber"].AutoSizeMode = Infragistics.Win.UltraWinGrid.ColumnAutoSizeMode.AllRowsInBand;
            grdResult.DisplayLayout.Bands[0].Columns["EntryTime"].Header.Caption = "Thời gian vào";
            grdResult.DisplayLayout.Bands[0].Columns["EntryTime"].Format = "dd/MM/yyyy HH:mm:ss";
            grdResult.DisplayLayout.Bands[0].Columns["EntryTime"].AutoSizeMode = Infragistics.Win.UltraWinGrid.ColumnAutoSizeMode.AllRowsInBand;
            grdResult.DisplayLayout.Bands[0].Columns["EntryTime"].Width = 150;
            grdResult.DisplayLayout.Bands[0].Columns["ExitTime"].Header.Caption = "Thời gian ra";
            grdResult.DisplayLayout.Bands[0].Columns["ExitTime"].Format = "dd/MM/yyyy HH:mm:ss";
            grdResult.DisplayLayout.Bands[0].Columns["ExitTime"].AutoSizeMode = Infragistics.Win.UltraWinGrid.ColumnAutoSizeMode.AllRowsInBand;
            grdResult.DisplayLayout.Bands[0].Columns["ExitTime"].Width = 150;
            grdResult.DisplayLayout.Bands[0].Columns["FrontImageEntry"].Hidden = true;
            grdResult.DisplayLayout.Bands[0].Columns["FrontImageExit"].Hidden = true;
            grdResult.DisplayLayout.Bands[0].Columns["RearImageEntry"].Hidden = true;
            grdResult.DisplayLayout.Bands[0].Columns["RearImageExit"].Hidden = true;            
            grdResult.DisplayLayout.Override.RowSizing = RowSizing.AutoFree;
            grdResult.DisplayLayout.Override.ColumnAutoSizeMode = ColumnAutoSizeMode.VisibleRows;
            grdResult.DisplayLayout.Override.ColumnSizingArea = ColumnSizingArea.EntireColumn;
            grdResult.DisplayLayout.Override.AllowAddNew = AllowAddNew.No;
            grdResult.DisplayLayout.Override.AllowUpdate = Infragistics.Win.DefaultableBoolean.False;
            grdResult.DisplayLayout.Override.AllowDelete = Infragistics.Win.DefaultableBoolean.False;
            grdResult.DisplayLayout.Override.RowSelectors = Infragistics.Win.DefaultableBoolean.True;
            grdResult.DisplayLayout.Override.RowSelectorNumberStyle = RowSelectorNumberStyle.RowIndex;
            grdResult.DisplayLayout.Override.SelectTypeRow = SelectType.Single;
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            object cardTypeID = DBNull.Value;
            if (cboCardType.SelectedIndex > 0)
            {             
                cardTypeID = cboCardType.Value;
            }
            object plateNumber = Common.DBNullValue(txtPlateNumber.Text.Trim());
            object entryFrom = dtpEntryFrom.Value;
            if (entryFrom == null) entryFrom = DBNull.Value;
            object entryTo = dtpEntryTo.Value;
            if (entryTo == null) entryTo = DBNull.Value;
            object exitFrom = dtpExitFrom.Value;
            if (exitFrom == null) exitFrom = DBNull.Value;
            object exitTo = dtpExitTo.Value;
            if (exitTo == null) exitTo = DBNull.Value;
            grdResult.DataSource = SearchData(cardTypeID, plateNumber, entryFrom, entryTo, exitFrom, exitTo);
            grdResult.Focus();
            if (grdResult.Rows.Count > 0)
            {
                grdResult.Rows[0].Selected = true;
            }
        }

        private void grdResult_AfterSelectChange(object sender, AfterSelectChangeEventArgs e)
        {
            if (grdResult.Selected.Rows.Count == 0) return;
            grpEntrance.Text = String.Format("Giờ vào: {0}", grdResult.Selected.Rows[0].Cells["EntryTime"].Value);
            grpExit.Text = String.Format("Giờ ra: {0}", grdResult.Selected.Rows[0].Cells["ExitTime"].Value);

            picFrontEntrance.Image = grdResult.Selected.Rows[0].Cells["FrontImageEntry"].Value;
            picEndEntrance.Image = grdResult.Selected.Rows[0].Cells["RearImageEntry"].Value;
            picFrontExit.Image = grdResult.Selected.Rows[0].Cells["FrontImageExit"].Value;
            picEndExit.Image = grdResult.Selected.Rows[0].Cells["RearImageExit"].Value;
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        
        #endregion

        #region Constructors
        public SearchCar()
        {
            InitializeComponent();
        }
        #endregion

        
    }
}
